package com.greentravel.dao.impl;

import com.greentravel.dao.TravelRecordDAO;
import com.greentravel.model.TravelRecord;
import com.greentravel.util.DatabaseUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 出行记录数据访问对象实现类
 * 实现对出行记录数据的具体操作
 */
public class TravelRecordDAOImpl implements TravelRecordDAO {

    @Override
    public boolean addTravelRecord(TravelRecord record) {
        String sql = "INSERT INTO travel_records(user_id, travel_time, travel_mode, start_location, end_location, distance, carbon_reduction, points) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, record.getUserId());
            pstmt.setTimestamp(2, new Timestamp(record.getTravelTime().getTime()));
            pstmt.setString(3, record.getTravelMode());
            pstmt.setString(4, record.getStartLocation());
            pstmt.setString(5, record.getEndLocation());
            pstmt.setDouble(6, record.getDistance());
            pstmt.setDouble(7, record.getCarbonReduction());
            pstmt.setInt(8, record.getPoints());

            int result = pstmt.executeUpdate();
            return result > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.closeResources(conn, pstmt, null);
        }
    }

    @Override
    public boolean deleteTravelRecord(int id) {
        String sql = "DELETE FROM travel_records WHERE id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            int result = pstmt.executeUpdate();
            return result > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.closeResources(conn, pstmt, null);
        }
    }

    @Override
    public boolean updateTravelRecord(TravelRecord record) {
        String sql = "UPDATE travel_records SET travel_time = ?, travel_mode = ?, start_location = ?, end_location = ?, distance = ?, carbon_reduction = ?, points = ? WHERE id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setTimestamp(1, new Timestamp(record.getTravelTime().getTime()));
            pstmt.setString(2, record.getTravelMode());
            pstmt.setString(3, record.getStartLocation());
            pstmt.setString(4, record.getEndLocation());
            pstmt.setDouble(5, record.getDistance());
            pstmt.setDouble(6, record.getCarbonReduction());
            pstmt.setInt(7, record.getPoints());
            pstmt.setInt(8, record.getId());

            int result = pstmt.executeUpdate();
            return result > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.closeResources(conn, pstmt, null);
        }
    }

    @Override
    public TravelRecord getTravelRecordById(int id) {
        String sql = "SELECT * FROM travel_records WHERE id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        TravelRecord record = null;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                record = new TravelRecord();
                record.setId(rs.getInt("id"));
                record.setUserId(rs.getInt("user_id"));
                record.setTravelTime(new Date(rs.getTimestamp("travel_time").getTime()));
                record.setTravelMode(rs.getString("travel_mode"));
                record.setStartLocation(rs.getString("start_location"));
                record.setEndLocation(rs.getString("end_location"));
                record.setDistance(rs.getDouble("distance"));
                record.setCarbonReduction(rs.getDouble("carbon_reduction"));
                record.setPoints(rs.getInt("points"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeResources(conn, pstmt, rs);
        }

        return record;
    }

    @Override
    public List<TravelRecord> getTravelRecordsByUserId(int userId) {
        String sql = "SELECT * FROM travel_records WHERE user_id = ? ORDER BY travel_time DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<TravelRecord> recordList = new ArrayList<>();

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                TravelRecord record = new TravelRecord();
                record.setId(rs.getInt("id"));
                record.setUserId(rs.getInt("user_id"));
                record.setTravelTime(new Date(rs.getTimestamp("travel_time").getTime()));
                record.setTravelMode(rs.getString("travel_mode"));
                record.setStartLocation(rs.getString("start_location"));
                record.setEndLocation(rs.getString("end_location"));
                record.setDistance(rs.getDouble("distance"));
                record.setCarbonReduction(rs.getDouble("carbon_reduction"));
                record.setPoints(rs.getInt("points"));
                recordList.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeResources(conn, pstmt, rs);
        }

        return recordList;
    }

    @Override
    public List<TravelRecord> getAllTravelRecords() {
        String sql = "SELECT * FROM travel_records ORDER BY travel_time DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<TravelRecord> recordList = new ArrayList<>();

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                TravelRecord record = new TravelRecord();
                record.setId(rs.getInt("id"));
                record.setUserId(rs.getInt("user_id"));
                record.setTravelTime(new Date(rs.getTimestamp("travel_time").getTime()));
                record.setTravelMode(rs.getString("travel_mode"));
                record.setStartLocation(rs.getString("start_location"));
                record.setEndLocation(rs.getString("end_location"));
                record.setDistance(rs.getDouble("distance"));
                record.setCarbonReduction(rs.getDouble("carbon_reduction"));
                record.setPoints(rs.getInt("points"));
                recordList.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeResources(conn, pstmt, rs);
        }

        return recordList;
    }

    @Override
    public List<TravelRecord> getTravelRecordsByDateRange(int userId, Date startDate, Date endDate) {
        String sql = "SELECT * FROM travel_records WHERE user_id = ? AND travel_time BETWEEN ? AND ? ORDER BY travel_time DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<TravelRecord> recordList = new ArrayList<>();

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            pstmt.setTimestamp(2, new Timestamp(startDate.getTime()));
            pstmt.setTimestamp(3, new Timestamp(endDate.getTime()));
            rs = pstmt.executeQuery();

            while (rs.next()) {
                TravelRecord record = new TravelRecord();
                record.setId(rs.getInt("id"));
                record.setUserId(rs.getInt("user_id"));
                record.setTravelTime(new Date(rs.getTimestamp("travel_time").getTime()));
                record.setTravelMode(rs.getString("travel_mode"));
                record.setStartLocation(rs.getString("start_location"));
                record.setEndLocation(rs.getString("end_location"));
                record.setDistance(rs.getDouble("distance"));
                record.setCarbonReduction(rs.getDouble("carbon_reduction"));
                record.setPoints(rs.getInt("points"));
                recordList.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeResources(conn, pstmt, rs);
        }

        return recordList;
    }

    @Override
    public List<TravelRecord> getTravelRecordsByTravelMode(int userId, String travelMode) {
        String sql = "SELECT * FROM travel_records WHERE user_id = ? AND travel_mode = ? ORDER BY travel_time DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<TravelRecord> recordList = new ArrayList<>();

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            pstmt.setString(2, travelMode);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                TravelRecord record = new TravelRecord();
                record.setId(rs.getInt("id"));
                record.setUserId(rs.getInt("user_id"));
                record.setTravelTime(new Date(rs.getTimestamp("travel_time").getTime()));
                record.setTravelMode(rs.getString("travel_mode"));
                record.setStartLocation(rs.getString("start_location"));
                record.setEndLocation(rs.getString("end_location"));
                record.setDistance(rs.getDouble("distance"));
                record.setCarbonReduction(rs.getDouble("carbon_reduction"));
                record.setPoints(rs.getInt("points"));
                recordList.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeResources(conn, pstmt, rs);
        }

        return recordList;
    }

    @Override
    public double getTotalDistanceByUserId(int userId) {
        String sql = "SELECT SUM(distance) AS total_distance FROM travel_records WHERE user_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        double totalDistance = 0;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                totalDistance = rs.getDouble("total_distance");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeResources(conn, pstmt, rs);
        }

        return totalDistance;
    }

    @Override
    public double getTotalCarbonReductionByUserId(int userId) {
        String sql = "SELECT SUM(carbon_reduction) AS total_carbon_reduction FROM travel_records WHERE user_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        double totalCarbonReduction = 0;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                totalCarbonReduction = rs.getDouble("total_carbon_reduction");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeResources(conn, pstmt, rs);
        }

        return totalCarbonReduction;
    }

    @Override
    public Map<String, Integer> getTravelModeStatistics(int userId) {
        String sql = "SELECT travel_mode, COUNT(*) AS count FROM travel_records WHERE user_id = ? GROUP BY travel_mode";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Map<String, Integer> statistics = new HashMap<>();

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                String travelMode = rs.getString("travel_mode");
                int count = rs.getInt("count");
                statistics.put(travelMode, count);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeResources(conn, pstmt, rs);
        }

        return statistics;
    }

    @Override
    public double getAverageDistanceByUserId(int userId) {
        String sql = "SELECT AVG(distance) AS average_distance FROM travel_records WHERE user_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        double averageDistance = 0;

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                averageDistance = rs.getDouble("average_distance");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.closeResources(conn, pstmt, rs);
        }

        return averageDistance;
    }
}